from openpyxl import Workbook 
from openpyxl import load_workbook
import math

def do_calc_popularity(view_count, fans):
    popularity = round(math.log2((view_count + 1) / fans), 3)
    #print("播放量：", view_count, "粉丝数：", fans, "流行度：", popularity)
    return popularity

info_path = "final/info.xlsx"
popular_path = "final/popular.xlsx"

info_ws = load_workbook(info_path).active
popular_wb = Workbook()
popular_ws = popular_wb.active

# 复制标题
for col in range(1, info_ws.max_column + 1):
    popular_ws.cell(1, col).value = info_ws.cell(1, col).value

# 添加流行度标题
popular_ws.cell(1, info_ws.max_column + 1).value = "流行度"

# 复制数据
start_row = 2
for item_idx in range(0, info_ws.max_row + 1 - 2): # skip title
    for col in range(1, info_ws.max_column + 1):
        popular_ws.cell(start_row, col).value = info_ws.cell(2 + item_idx, col).value
    # calc popular
    view_count = info_ws.cell(2 + item_idx, 5).value # column 5 是播放量
    fans = info_ws.cell(2 + item_idx, 16).value # column 16 是粉丝数

    popular_ws.cell(start_row, info_ws.max_column + 1).value = do_calc_popularity(view_count, fans)
    start_row += 1

popular_wb.save(popular_path)
print("[INFO] 计算流行度成功！")

import pandas as pd
data_test = pd.read_excel('final/popular.xlsx')
df = pd.DataFrame(data_test)
# 以列“Fruit”的标签列来进行升序排列
df_1 = df.sort_values('流行度',ascending=False)
writer =  pd.ExcelWriter('final/popular_sorted.xlsx')
df_1.to_excel(writer,sheet_name = 'Sheet1',index=False)
writer.save()

print("[INFO] 根据流行度排序成功！")
